# coding=UTF-8
import os

import openpyxl
from openpyxl import Workbook
from utils import parse_util

# 二维数组 content [['姓名', '性别', '年龄', '身高'], [1, 1, 1, 1],[2, 2, 2, 2]]
from openpyxl.utils import column_index_from_string


# content_arr = [['姓名','性别','年龄'],['小明','男','20'],['小霞','女','18']]
def gen_an_excel(file_path, excel_name, sheet_name, content_arr):
    wb = Workbook()
    ws = wb.active
    if sheet_name == '':
        sheet_name = 'sheet1'
    ws.title = sheet_name
    # print(content_arr)
    for i in range(len(content_arr)):
        for j in range(len(content_arr[i])):
            ws.cell(row=i + 1, column=j + 1).value = content_arr[i][j]
    # 保存
    os.makedirs(file_path, exist_ok=True)  # 创建一个文件夹
    wb.save(filename=os.path.join(file_path, excel_name + '.xlsx'))  # 保存文件
    wb.close()
    return wb


# 追加一列
# 一维数组 titles [['姓名', '性别', '年龄', '身高']]
# 二维数组 data [['小王', '小美', '小红', '小张'], ['男', '女', '女', '男'],[2, 2, 2, 2]]
def add_columns(file_path, sheet_name, titles, data):
    wb = openpyxl.load_workbook(file_path)
    ws = wb.worksheets[0]
    if sheet_name != '':
        ws = wb.get_sheet_by_name[sheet_name]
    column_len = ws.max_column
    for title in reversed(titles):
        ws.insert_cols(column_len + 1)
        ws.cell(row=1, column=column_len + 1, value=title)
    d_len = len(data)
    for d in range(d_len):
        info = data[d]
        c_len = len(info)
        for i in range(c_len):
            ws.cell(row=i + 2, column=column_len + d + 1, value=info[i])
    wb.save(file_path)  # 保存文件
    wb.close()


# 追加n行
# 二维数组 data [['小王', '小美', '小红', '小张'], ['男', '女', '女', '男'],[2, 2, 2, 2]]
def add_n_row(file_path, sheet_name, data):
    wb = openpyxl.load_workbook(file_path)
    ws = wb.worksheets[0]
    if sheet_name != '':
        ws = wb.get_sheet_by_name[sheet_name]
    row_len = ws.max_row
    data_len = len(data)
    for num in range(data_len):
        data_info = data[num]
        ws.insert_rows(row_len + num + 1)
        data_info_len = len(data_info)
        for num1 in range(data_info_len):
            ws.cell(row=row_len + num + 1, column=num1 + 1, value=data_info[num1])
    wb.save(file_path)  # 保存文件
    wb.close()


# 获取一列数据
def get_column(file_path, sheet_name, column):
    if type(column) is str:
        column = column_index_from_string(column)
    wb = openpyxl.load_workbook(file_path)
    ws = wb.worksheets[0]
    if sheet_name != '':
        # print(sheet_name)
        # print(file_path)
        ws = wb.get_sheet_by_name(sheet_name)
    rows = ws.max_row
    columndata = []
    for i in range(1, rows):
        cellvalue = ws.cell(row=i + 1, column=column).value
        columndata.append(cellvalue)
    wb.close()
    return columndata


# 获取一行数据row_data = {}
def get_row_with_title(file_path, sheet_name, row):
    wb = openpyxl.load_workbook(file_path)
    ws = wb.worksheets[0]
    if sheet_name != '':
        ws = wb.get_sheet_by_name[sheet_name]
    columns = ws.max_column
    row_data = {}
    for i in range(1, columns):
        cellvalue = ws.cell(row=row, column=i).value
        row_data[ws.cell(row=1, column=i).value] = cellvalue
    wb.close()
    return row_data


# 获取一行数据 row_data = []
def get_row_info(file_path, sheet_name, row):
    wb = openpyxl.load_workbook(file_path)
    ws = wb.worksheets[0]
    if sheet_name != '':
        ws = wb.get_sheet_by_name[sheet_name]
    columns = ws.max_column
    row_data = []
    for i in range(1, columns):
        cellvalue = ws.cell(row=row, column=i).value
        row_data.append(cellvalue)
    wb.close()
    return row_data


# 获取所有数据 result_data = [[]]
def get_excel_info(file_path, sheet_name):
    if file_path[-5:] != '.xlsx':
        file_path += '.xlsx'
    wb = openpyxl.load_workbook(file_path)
    ws = wb.worksheets[0]
    if sheet_name != '':
        ws = wb.get_sheet_by_name[sheet_name]
    columns = ws.max_column
    rows = ws.max_row
    result_data = []
    for i in range(1, rows + 1):
        row_data = []
        for j in range(1, columns + 1):
            cellvalue = ws.cell(row=i, column=j).value
            row_data.append(cellvalue)
        result_data.append(row_data)
    wb.close()
    return result_data


# 生成路径
def gen_an_excel_this_path(os, data_list, name):
    wb = Workbook()
    ws = wb.active
    sheet_name = 'sheet1'
    ws.title = sheet_name
    content_arr = parse_util.list_dict_to_arr(data_list)
    for i in range(len(content_arr)):
        for j in range(len(content_arr[i])):
            ws.cell(row=i + 1, column=j + 1).value = content_arr[i][j]
    # 保存
    file_path = os.path.join(os.getcwd(), 'excel')
    os.makedirs(file_path, exist_ok=True)  # 创建一个文件夹
    wb.save(filename=os.path.join(file_path, name + '.xlsx'))  # 保存文件
    wb.close()
    return wb


if __name__ == '__main__':
    print()
    # path = 'E:\\klun\\firstPython\\stock\\analysis\\双阳夹阴\\精选2020-06-09.xlsx'
    # # add_columns(path, '', ['a'], [[1, 2, 3, 4, 5, 6, 7, 8]])
    # # get_column(path,'','升幅(%)')
    # print(get_row_with_title(path, '', 2))
    # print(get_row_info(path, '', 2))
    # print(get_column(path, '', 'A'))
    # def add_n_row(file_path, sheet_name, data):
    # add_n_row("E:\\klun\\FirstPython\\stock\\result\\突破均线\\2020-12-11\\验证\\三一重工600031.xlsx", '', [[1, 1, 1, 1, 1]])
